package com.chpro.jdbc;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

/**
 * SqlServerJdbc ʵ����
 * @author Administrator
 *
 */
public class SqlServerJdbc implements MyJdbc {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<String> findTables() {
		String sql = "select name from sysobjects where type='U' order by name";
		List<String> result = jdbcTemplate.queryForList(sql,String.class);
		return result;
	}

	public List<Map<String,Object>> findColumns(String tableName) {
		String sql = 	" select column_name,data_type from information_schema.columns"+
						" where table_name = ?";
		return jdbcTemplate.queryForList(sql,tableName); 
	}

	public String findPropertyKey(String tableName) {
		String sql = 	" select COLUMN_NAME = convert(sysname,c.name) "+
						" from "+                                                       
						" sysindexes i, syscolumns c, sysobjects o "+                   
						" where o.id = object_id(?) "+                  
						" and o.id = c.id "+                                            
						" and o.id = i.id "+                                            
						" and (i.status & 0x800) = 0x800 "+                             
						" and (c.name = index_col (?, i.indid,  1) or "+     
						"      c.name = index_col (?, i.indid,  2) or "+     
						"      c.name = index_col (?, i.indid,  3) or "+     
						"      c.name = index_col (?, i.indid,  4) or "+     
						"      c.name = index_col (?, i.indid,  5) or "+     
						"      c.name = index_col (?, i.indid,  6) or "+     
						"      c.name = index_col (?, i.indid,  7) or "+     
						"      c.name = index_col (?, i.indid,  8) or "+     
						"      c.name = index_col (?, i.indid,  9) or "+     
						"      c.name = index_col (?, i.indid, 10) or "+     
						"      c.name = index_col (?, i.indid, 11) or "+     
						"      c.name = index_col (?, i.indid, 12) or "+     
						"      c.name = index_col (?, i.indid, 13) or "+     
						"      c.name = index_col (?, i.indid, 14) or "+     
						"      c.name = index_col (?, i.indid, 15) or "+     
						"      c.name = index_col (?, i.indid, 16) "+       
						"      )";
		
		String pk = jdbcTemplate.queryForObject(sql, String.class,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName,tableName);
		return pk;
	}

	public boolean createTable(String tableName,
			List<Map<String, String>> config) {
		String sql = 	"CREATE TABLE ["+tableName+"]( \n";

		for(Map<String, String> map:config){
			sql+="\t["+map.get("columnName")+"] ["+map.get("columnType")+"]("+map.get("columnSize")+") "+("true".equals(map.get("isPK"))?"primary key":"")+" "+("false".equals(map.get("isNULL"))?"NOT NULL":"NULL")+", \n";
		}
		sql=sql.substring(0,sql.length()-2);
		sql+="\n)"; 
		System.out.println(sql);
		jdbcTemplate.execute(sql);
		return true;
	}

}
